package com.blog.dao;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.blog.domain.Album;
import com.blog.domain.Blog;
import com.blog.domain.Category;
import com.blog.domain.Comment;
import com.blog.domain.Photo;
import com.blog.domain.User;

@Repository("BlogDao")
public class BLogQueryDao extends BlogJdbcDaoSupport{

	/**
	 * 查询玩家
	 * @param mailId
	 * @param password
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public User getUser(String mailId, String password) {
		StringBuffer sb = new StringBuffer("select userId,userName,password,photo from users ");
		sb.append(" where userId like '"+mailId+"' and password like '" +password +"'");
		System.out.println(sb);
		List<User> list = this.getJdbcTemplate().query(sb.toString(),
				new RowMapper() {
					public Object mapRow(ResultSet rs, int n) throws SQLException {
						User usr = new User();
						usr.setUserId(rs.getString("userId"));
						usr.setUserName(rs.getString("userName"));
						usr.setPassword(rs.getString("password"));
						usr.setPhoto(rs.getString("photo"));
						return usr;
					}
				});
        if(list==null||list.size()==0){return null;}
		return list.get(0);
	}
	/**
	 * 查询玩家
	 * @param mailId
	 * @param password
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public User getUserByName(String name) {
		StringBuffer sb = new StringBuffer("select userId,userName,password,photo from users ");
		sb.append(" where userName like '"+name+"'");
		System.out.println(sb);
		List<User> list = this.getJdbcTemplate().query(sb.toString(),
				new RowMapper() {
					public Object mapRow(ResultSet rs, int n) throws SQLException {
						User usr = new User();
						usr.setUserId(rs.getString("userId"));
						usr.setUserName(rs.getString("userName"));
						usr.setPassword(rs.getString("password"));
						usr.setPhoto(rs.getString("photo"));
						return usr;
					}
				});
        if(list==null||list.size()==0){return null;}
		return list.get(0);
	}
	
	/**
	 * 查询博客
	 * @return
	 */
	public List<Blog> getBlogs(int start,int end){
		StringBuffer sql = new StringBuffer(
				"select b.id as id,b.title as title," +
				"b.content as content,b.time as time," +
				"c.id as cid,c.name as cateName,b.old as flag  " +
				"from blogs b,category c where ishow = 1 and b.cateId=c.id order by b.id desc  ");
		if(end>start && start>=0) sql.append("limit "+start+","+end+";");
		System.out.println(sql);
		List<Blog> list = this.getJdbcTemplate().query(sql.toString(),new RowMapperToBlog());
		return list;
	}
	
	/**
	 * 查询博客
	 * @return
	 */
	public Blog getTopBlog(){
		StringBuffer sql = new StringBuffer(
				"select b.id as id,b.title as title," +
				"b.content as content,b.time as time," +
				"c.id as cid,c.name as cateName,b.old as flag  " +
				"from blogs b,category c " +
				"where ishow = 1 and b.cateId=c.id " +
				"order by b.id desc " +
				"limit 0, 1 ");
		System.out.println(sql);
		Blog blog =  this.getJdbcTemplate().queryForObject(sql.toString(),new RowMapperToBlog());
		return blog;
	}
	static class RowMapperToBlog implements RowMapper<Blog> {
		public Blog mapRow(ResultSet rs, int arg1) throws SQLException {
			Blog blog = new Blog();
			blog.setId(rs.getInt("id"));
			blog.setTitle(rs.getString("title"));
			blog.setContent(rs.getString("content"));
			blog.setCreatedTime(rs.getDate("time"));
			blog.setCategoryId(rs.getInt("cid"));			
			blog.setCategory(rs.getString("cateName"));			
			blog.setOld(rs.getInt("flag"));
			return blog;
		}
		
	}
	/**
	 * 查询博客
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<Blog> getBlogsByCateId(int cid){
		StringBuffer sql = new StringBuffer(
				"select b.id as id,b.title as title," +
				"b.content as content,b.time as time," +
				"c.id as cid,c.name as cateName,b.old as flag  " +
				"from blogs b,category c where ishow = 1 and b.cateId=c.id " +
				"and b.cateId = "+cid+" order by b.id desc  ");
		
		System.out.println(sql);
		List<Blog> list = this.getJdbcTemplate().query(sql.toString(),
				new RowMapper() {
					public Object mapRow(ResultSet rs, int n) throws SQLException {
						Blog blog = new Blog();
						blog.setId(rs.getInt("id"));
						blog.setCategoryId(rs.getInt("cid"));
						blog.setTitle(rs.getString("title"));
						blog.setCategory(rs.getString("cateName"));
						blog.setContent(rs.getString("content"));
						blog.setCreatedTime(rs.getDate("time"));
						blog.setOld(rs.getInt("flag"));
						return blog;
					}
				});
		return list;
	}
	/**
	 * 查询博客
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public Blog getBlog(int id){
		StringBuffer sql = new StringBuffer(
				"select b.id as id,b.title as title," +
				"b.content as content,b.time as time," +
				"c.id as cid,c.name as cateName ,b.old as flag  " +
				"from blogs b,category c where  ishow = 1 and b.cateId=c.id  and b.id ="+id );
		System.out.println(sql);
		List<Blog> list = this.getJdbcTemplate().query(sql.toString(),
				new RowMapper() {
					public Object mapRow(ResultSet rs, int n) throws SQLException {
						Blog blog = new Blog();
						blog.setId(rs.getInt("id"));
						blog.setCategoryId(rs.getInt("cid"));
						blog.setTitle(rs.getString("title"));
						blog.setCategory(rs.getString("cateName"));
						blog.setContent(rs.getString("content"));
						blog.setCreatedTime(rs.getDate("time"));
						blog.setOld(rs.getInt("flag"));
						return blog;
					}
				});
		if(list!=null) return list.get(0);
		return null;
	}
	/**
	 * 查询分类
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public List<Category> getCategorys(){
		StringBuffer sql = new StringBuffer("select id,name from category order by id desc");
		System.out.println(sql);
		List<Category> list = this.getJdbcTemplate().query(sql.toString(),
				new RowMapper() {
					public Object mapRow(ResultSet rs, int n) throws SQLException {
						Category category = new Category();
						category.setId(rs.getInt("id"));						
						category.setName(rs.getString("name"));
						return category;
					}
				});
		return list;
	}
	/**
	 * 查询评论
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<Comment> getComments(int blogId){
		StringBuffer sql = new StringBuffer("select id,username,content,blogId,time " +
				" from comments ");
		if(blogId>0) sql.append(" where blogId="+blogId);
		sql.append(" order by id desc  ");
		System.out.println(sql);
		List<Comment> list = this.getJdbcTemplate().query(sql.toString(),
				new RowMapper() {
					public Object mapRow(ResultSet rs, int n) throws SQLException {
						Comment comment = new Comment();
						comment.setId(rs.getInt("id"));
						comment.setContent(rs.getString("content"));
						comment.setUsername(rs.getString("username"));
						comment.setBlogId(rs.getInt("blogId"));
						comment.setCreatedTime(rs.getTimestamp("time"));
						return comment;
					}
				});
		return list;
	}
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<Comment> getFirst10Comments(){
		StringBuffer sql = new StringBuffer("select id,username,content,blogId,time " +
				" from comments ");
		sql.append(" order by id desc  limit 10");
		System.out.println(sql);
		List<Comment> list = this.getJdbcTemplate().query(sql.toString(),
				new RowMapper() {
					public Object mapRow(ResultSet rs, int n) throws SQLException {
						Comment comment = new Comment();
						comment.setId(rs.getInt("id"));
						comment.setContent(rs.getString("content"));
						comment.setUsername(rs.getString("username"));
						comment.setBlogId(rs.getInt("blogId"));
						comment.setCreatedTime(rs.getTimestamp("time"));
						return comment;
					}
				});
		return list;
	}
	
	/**
	 * 查询相册
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<Album> getAlbumDetails(){
		StringBuffer sql = new StringBuffer("select a.id,a.name,a.comment,a.cover,a.isShow,count(p.id) as num" +
				" from albums a,photos p where p.albumId = a.id group by a.id order by a.time ");
		System.out.println(sql);
		List<Album> list = this.getJdbcTemplate().query(sql.toString(),
				new RowMapper() {
					public Object mapRow(ResultSet rs, int n) throws SQLException {
						Album album = new Album();
						album.setId(rs.getInt("id"));
						album.setComment(rs.getString("comment"));
						album.setName(rs.getString("name"));
						album.setCover(rs.getString("cover"));
						album.setShow(rs.getInt("isShow"));
						album.setPicNum(rs.getInt("num"));
						return album;
					}
				});
		return list;
	}
	
	/**
	 * 查询相册分类
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<Album> getAlbums(){
		StringBuffer sql = new StringBuffer("select a.id,a.name from albums a order by a.id");
		System.out.println(sql);
		List<Album> list = this.getJdbcTemplate().query(sql.toString(),
				new RowMapper() {
					public Object mapRow(ResultSet rs, int n) throws SQLException {
						Album album = new Album();
						album.setId(rs.getInt("id"));
						album.setName(rs.getString("name"));
						return album;
					}
				});
		return list;
	}
	/**
	 * 查询照片
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<Photo> getPhotos(){
		StringBuffer sql = new StringBuffer("select id,albumId,name,comment,path " +
				" from photos order by id desc ");
		System.out.println(sql);
		List<Photo> list = this.getJdbcTemplate().query(sql.toString(),
				new RowMapper() {
					public Object mapRow(ResultSet rs, int n) throws SQLException {
						Photo photo = new Photo();
						photo.setId(rs.getInt("id"));
						photo.setComment(rs.getString("comment"));
						photo.setName(rs.getString("name"));
						photo.setPath(rs.getString("path"));
						photo.setAlbumId(rs.getInt("albumId"));
						return photo;
					}
				});
		return list;
	}
	
	public int insertCategory(Category cate){
		String sql = "insert into category(name,level) values(?,?)";
		System.out.println(sql);
		return this.getJdbcTemplate().update(sql,new Object[]{cate.getName(),cate.getLevel()});
	}
	
	public int insertBlog(Blog blog){
		String sql = "insert into blogs(title,content,time,cateId) values(?,?,?,?)";
		System.out.println(sql);
		return this.getJdbcTemplate().update(sql,new Object[]
				{blog.getTitle(),blog.getContent(),new Date(System.currentTimeMillis()),blog.getCategoryId()});
	}
	public int insertPhoto(Photo photo){
		String sql = "insert into photos(albumId, name, comment, path) values(?,?,?,?)";
		System.out.println(sql);
		return this.getJdbcTemplate().update(sql,new Object[]
				{photo.getAlbumId(),photo.getName(),photo.getComment(),photo.getPath()});
	}
	public int insertAlbum(Album album){
		String sql = "insert into albums(name, comment, cover,isShow,time,picnum) values(?,?,?,?,?,?)";
		System.out.println(sql);
		return this.getJdbcTemplate().update(sql,new Object[]
				{album.getName(),album.getComment(),
				album.getCover(),album.getShow(),
				new Date(System.currentTimeMillis()),
				album.getPicNum()});
	}
	public int insertComment(Comment com){
		String sql = "insert into comments(blogId, username,content,time) values(?,?,?,?)";
		System.out.println(sql);
		return this.getJdbcTemplate().update(sql,new Object[]
				{com.getBlogId(),com.getUsername(),com.getContent(),com.getCreatedTime()});
	}
	public int updateUserPhoto(String uid,String path){
		String p = path.replaceAll("\\\\", "/");
		String sql = "update users set photo = '"+p+"' where userId = '"+uid+"'";
		System.out.println(sql);
		return this.getJdbcTemplate().update(sql);
	}
}
